/**
 * Company : Thai Informatic System Co., Ltd.
 * Created By : Chatree Siangklom
 * Created On : May 28, 2013  5:22:34 PM 
 * e-Mail : dr_chatree@hotmail.com
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : com.tis.std.synchro.queue
 * File Name : QueueToHist.java
 * TODO
 */
package com.tis.std;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import promis.db.connection.dbHelper;
import promis.utility.CommonService;
import promis.utility.Utilitys;

/**
 * @author chatree
 *
 */
public class QueueToHist {
	
	
	private static final Logger log = Logger.getLogger(QueueToHist.class);
	
	/**
	 * 
	 * @return
	 * @throws Exception
	 */
	public List<String> QueueGroup() throws Exception {
		List<String> queueList = new ArrayList<String>(); 
		try{
			StringBuffer _strQuery = new StringBuffer();			
			_strQuery.append(" select QUEUE_ID  ");
			_strQuery.append(" from SYNC_QUEUE_PARAM_LOGS ");
			_strQuery.append(" where NVL(IS_HIST,'N') = 'N'  ");
			_strQuery.append(" group by QUEUE_ID  ");
			_strQuery.append(" order by QUEUE_ID ");					
			System.out.println(_strQuery.toString().toUpperCase());
			log.info(_strQuery.toString().toUpperCase());
			
			Connection conn = new dbHelper().getConn();
			Statement stmt = conn.createStatement();				
			try{				
				ResultSet rs = stmt.executeQuery(_strQuery.toString());				
				if(rs != null){
					while(rs.next()){
						queueList.add(rs.getString("QUEUE_ID"));
					}				
				}
				rs.close();
			}catch(Exception e){
				e.printStackTrace();
				System.err.println(e.getMessage());
			}finally{
				stmt.close();
	            conn.close();
	            stmt = null;
	            conn = null;
			}
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getStackTrace());
		}
		return queueList;
	}
	
	public void TransformToHist(String queueId, Connection conn) throws Exception {
		ResultSet rs = null;
		try{			
			conn.setAutoCommit(false);			
			
			StringBuffer _strQuery = new StringBuffer();						
			_strQuery.append(" SELECT distinct ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'company') as company, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'project') as project, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'documentNo') as documentNo, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'contractRefNo') as contractRefNo, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'buildingPhase') as buildingPhase, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'CN_DocumentNo') as CN_DocumentNo, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'sapPlantCode') as sapPlantCode, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'processAction') as processAction, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'syncIsstaging') as syncIsstaging, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'unitNumber') as unitNumber, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'bs_cs_status') as bs_cs_status, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'refType') as refType, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'status') as status, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'customerCode') as customerCode, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'contractNo') as contractNo, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'ReceipteddocumentNo') as ReceipteddocumentNo, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'zoneCode') as zoneCode, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'processActionBy') as processActionBy, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'BookingOrContarctNo') as BookingOrContarctNo, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'action') as action, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'matritalCode') as matritalCode, ");
			_strQuery.append(" (select x.PARAM_VALUE from SYNC_QUEUE_PARAM_LOGS x Where a.QUEUE_ID = x.QUEUE_ID and x.PARAM_NAME = 'receipt_status') as receipt_status, ");
			_strQuery.append(" a.QUEUE_ID, ");
			_strQuery.append(" a.IS_PROCESSING, ");
			_strQuery.append(" a.PROC_CREATEDBY, ");
			_strQuery.append(" b.PROCESSING_NAME, ");
			_strQuery.append(" to_char(a.PROC_CREATEDON, 'YYYY-MM-DD HH24:MI:SS') as PROC_CREATEDON ");
			_strQuery.append(" FROM SYNC_QUEUE_PARAM_LOGS a ");
			_strQuery.append(" inner join SYNC_QUEUE_LOGS b on (a.QUEUE_ID = b.QUEUE_ID) ");
			_strQuery.append(" WHERE a.QUEUE_ID =  ").append(queueId);
			//System.out.println(_strQuery.toString());
			log.info(_strQuery.toString().toUpperCase());
			
			//conn.commit();			
			
			if(conn == null)
				throw new Exception("Connection is null !!!".toUpperCase());
			
			Statement stmt = conn.createStatement();
			Statement stmt1 = conn.createStatement();
			Statement stmt2 = conn.createStatement();
			try{				
				rs = stmt.executeQuery(_strQuery.toString());				
				if(rs != null){
					while(rs.next()){
						_strQuery = new StringBuffer();						
						_strQuery.append(" INSERT INTO SYNCHRO.SYNC_QUEUE_HIST ( ");
						_strQuery.append(" SYNC_QUEUE_HIST_ID, QUEUE_ID, BOOKINGORCONTARCTNO, "); 
						_strQuery.append(" CN_DOCUMENTNO, RECEIPTEDDOCUMENTNO, ACTION,  ");
						_strQuery.append(" BS_CS_STATUS, BUILDINGPHASE, COMPANY,  ");
						_strQuery.append(" CONTRACTNO, CONTRACTREFNO, CUSTOMERCODE,  ");
						_strQuery.append(" DOCUMENTNO, MATRITALCODE, PROJECT,  ");
						_strQuery.append(" RECEIPT_STATUS, REFTYPE, SAPPLANTCODE,  ");
						_strQuery.append(" STATUS, SYNCISSTAGING, UNITNUMBER,  ");
						_strQuery.append(" ZONECODE, PROC_CREATEDON, PROC_CREATEDBY, "); 
						_strQuery.append(" IS_PROCESSING, PROCESSING_NAME)  ");
						_strQuery.append(" VALUES ( SYNC_QUEUE_HIST_SEQ.NEXTVAL, ");
						_strQuery.append(" "+ rs.getString("QUEUE_ID") +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("BOOKINGORCONTARCTNO")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("CN_DOCUMENTNO")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("RECEIPTEDDOCUMENTNO")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("ACTION")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("BS_CS_STATUS")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("BUILDINGPHASE")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("COMPANY")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("CONTRACTNO")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("CONTRACTREFNO")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("CUSTOMERCODE")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("DOCUMENTNO")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("MATRITALCODE")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("PROJECT")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("RECEIPT_STATUS")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("REFTYPE")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("SAPPLANTCODE")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("STATUS")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("SYNCISSTAGING")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("UNITNUMBER")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("ZONECODE")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("PROC_CREATEDON")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("PROC_CREATEDBY")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("IS_PROCESSING")) +", ");
						_strQuery.append(" "+ Utilitys.strSql(rs.getString("PROCESSING_NAME")) +" ) ");						
						System.out.println(_strQuery.toString());
						stmt1.execute(_strQuery.toString());					
						
						_strQuery = new StringBuffer();
						_strQuery.append(" UPDATE SYNCHRO.SYNC_QUEUE_PARAM_LOGS SET IS_HIST = 'Y' WHERE QUEUE_ID = ".toUpperCase()).append(queueId);
						System.out.println(_strQuery.toString());
						stmt2.execute(_strQuery.toString());
						
						conn.commit();
					}
				}
				
			}catch(Exception e){
				
				if(!conn.isClosed() && conn != null)
	            	conn.rollback();
				
				e.printStackTrace();
				System.err.println(e.getMessage());
			}finally{
				
				if(stmt != null)
					stmt.close();
				
				if(stmt1 != null)
					stmt1.close();
				
				if(stmt2 != null)
					stmt2.close();
				
				if(conn != null || conn.isClosed() != true)
					conn.setAutoCommit(true);	
				
				rs = null;
			}			
		}catch(Exception e){
			e.printStackTrace();
			log.error(e.getStackTrace());
		}
		return;
	}
	
	private static final long MEGABYTE = 1024L * 1024L;

	public static long bytesToMegabytes(long bytes) {		  		  
		  return (bytes / MEGABYTE);
	}
	
	/**
	 * 
	 * Date =Nov 27, 2013 : 10:26:24 AM 
	 * CreateBy=Chatree Siangklom
	 * void
	 * TODO
	 * @throws Exception
	 */
	public void QueueExec() throws Exception {
		try{	
			int irow = 0;
			while(true)
			 {
				List<String> lst = QueueGroup();
				System.out.println("Queue size : " + CommonService.getSize(lst));
				if(CommonService.getSize(lst) > 0){
					dbHelper  dbHelp = new dbHelper();
					Connection conn = dbHelp.getConn();
					
					for(String queueId : lst){	
						irow++;
						if(conn == null)
							 conn = dbHelp.getConn();
						 
						 if(conn.isClosed())
							 conn = dbHelp.getConn();
						
						TransformToHist(queueId, conn);
						
						System.err.println(irow + " No. Insert to queu hist by queue-Id =".toUpperCase() + queueId + " ---> Seccess" .toUpperCase());
						log.info(irow + " No. Insert to queu hist by queue-Id =".toUpperCase() + queueId + " ---> Seccess" .toUpperCase());
						log.info("#######################################################################");
						System.err.println("#######################################################################");
						//System.in.read();
					}
				}

				// Get the Java runtime
				 Runtime runtime = Runtime.getRuntime();
				 
			    // Calculate the used memory		    
				long memory = runtime.totalMemory() - runtime.freeMemory();
				System.out.println("totalMemory: " + bytesToMegabytes(runtime.totalMemory()) + "MB." 
	    							+ " //freeMemory: " + bytesToMegabytes(runtime.freeMemory()) + "MB." 
	    							+ " //Used memory is bytes: " +  memory
	    							+ " //Used memory is megabytes: " + bytesToMegabytes(memory)  + "MB.".toUpperCase());
				
				log.info("totalMemory: " + bytesToMegabytes(runtime.totalMemory()) + "MB." 
						+ " //freeMemory: " + bytesToMegabytes(runtime.freeMemory()) + "MB." 
						+ " //Used memory is bytes: " +  memory
						+ " //Used memory is megabytes: " + bytesToMegabytes(memory)  + "MB.".toUpperCase());
				    
			    // Run the garbage collector
			    runtime.gc();
			    //System.gc();
			    Thread.sleep(5000);
			 }		
		}catch(Exception e)
		{
			e.printStackTrace();
			log.error(e.getStackTrace());
		}
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {		
		try{
			
			new QueueToHist().QueueExec();
			
		}catch(Exception e)
		{
			e.printStackTrace();
			log.error(e.getStackTrace());
		}
	}

}
